package model.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.record.formula.functions.Hlookup;

import com.mysql.jdbc.PreparedStatement;

import model.pojo.Account;
import model.util.MySqlDataAccessHelper;

public class AccountDAO {
	MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
	int flag = 0;

	public int saveAccount(Account account){
		String sql = "insert into account(Acct,DescVN,DescKR,DescEN,AcctType,ClassID,Status,IsGa) values(?,?,?,?,?,?,?,?)";
		PreparedStatement ps;
		try {
			helper.open();
			ps = (PreparedStatement) helper.getConnection().prepareStatement(sql);
			ps.setString(1, account.getAccount());
			ps.setString(2, account.getDescrVN());
			ps.setString(3, account.getDescrEN());
			ps.setString(4, account.getDescrKR());
			ps.setString(5, account.getAccountType());
			ps.setString(6, account.getClassID());
			ps.setString(7, account.getStatus());
			ps.setBoolean(8, account.isGa());
			int x = ps.executeUpdate();
			if(x>0)
				flag = 1;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			helper.close();
		}
		return flag;
	}
	// get all account
	public List<Account> getAllAccount(){
		List<Account> lstAccount = new ArrayList<Account>();
		try {
			helper.open();
			String query = "select * from account";
			ResultSet rs = helper.executeQuery(query);
			while(rs.next()){
				Account account = new Account();
				account.setAccount(rs.getString("Acct"));
				account.setDescrVN(rs.getString(",DescVN"));
				account.setDescrEN(rs.getString(",DescKR"));
				account.setDescrKR(rs.getString(",DescEN"));
				account.setAccountType(rs.getString("AcctType"));
				account.setClassID(rs.getString("ClassID"));
				account.setStatus(rs.getString("Status"));

				lstAccount.add(account);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			helper.close();
		}
		return lstAccount;
	}
	// delete account
	public int deleteAccount(String acc){
		try{
			flag = 0;
			helper.open();
			String sql = "delete from account where Acct = '"+acc+"'";
			int x = helper.executeUpdate(sql);
			if(x>0)
				flag = 1;
		}catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}

	// update account
	public int updateAccount(Account account){
		try{
			flag = 0;
			helper.open();
			String sql = "update account set DescVN=?,DescKR=?,DescEN=?,AcctType=?,ClassID=?,Status=? Where Acct=?";
			PreparedStatement ps = (PreparedStatement)helper.getConnection().prepareCall(sql);
				ps.setString(1, account.getDescrVN());
				ps.setString(2,account.getDescrKR());
				ps.setString(3, account.getDescrEN());
				ps.setString(4, account.getAccountType());
				ps.setString(5,account.getClassID());
				ps.setString(6, account.getStatus());
				ps.setString(7, account.getAccount());
				
				int x = ps.executeUpdate(sql);
				if(x>0)
					flag = 1;
		}catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}

}
